/*
MySQL Backup
Database: course
Backup Time: 2020-06-14 10:59:13
*/

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `course`.`admin`;
DROP TABLE IF EXISTS `course`.`class`;
DROP TABLE IF EXISTS `course`.`course`;
DROP TABLE IF EXISTS `course`.`department`;
DROP TABLE IF EXISTS `course`.`major`;
DROP TABLE IF EXISTS `course`.`student`;
DROP TABLE IF EXISTS `course`.`student_course`;
DROP TABLE IF EXISTS `course`.`teacher`;
CREATE TABLE `admin` (
  `admin_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '管理员Id',
  `admin_username` varchar(255) NOT NULL COMMENT '用户名',
  `admin_password` char(32) NOT NULL COMMENT '密码',
  `admin_privilege` int(11) NOT NULL COMMENT '角色\r\n二进制表示权限\r\n1-系管理\r\n2-专业管理\r\n4-班级管理\r\n8-学生管理\r\n16-教师管理\r\n32-课程管理\r\n64-选课管理\r\n128-管理员管理',
  PRIMARY KEY (`admin_id`) USING BTREE,
  UNIQUE KEY `idx_admin_username` (`admin_username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE `class` (
  `class_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '班级Id',
  `class_major_id` int(10) unsigned NOT NULL COMMENT '专业Id',
  `class_grade` int(10) unsigned NOT NULL COMMENT '年级',
  `class_name` varchar(32) NOT NULL COMMENT '班级名称',
  PRIMARY KEY (`class_id`) USING BTREE,
  KEY `fk_major_id` (`class_major_id`) USING BTREE,
  KEY `idx_class_name` (`class_name`) USING BTREE,
  CONSTRAINT `fk_major_id` FOREIGN KEY (`class_major_id`) REFERENCES `major` (`major_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE `course` (
  `course_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程Id',
  `course_teacher_id` int(10) unsigned NOT NULL COMMENT '授课教师Id',
  `course_name` varchar(64) NOT NULL COMMENT '课程名称',
  `course_grade` int(10) unsigned NOT NULL COMMENT '授课年级',
  `course_time` varchar(16) NOT NULL COMMENT '上课时间 星期几-第几节-几节课',
  `course_location` varchar(32) NOT NULL COMMENT '上课地址',
  `course_credit` int(10) unsigned NOT NULL COMMENT '学分',
  `course_selected_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '已选人数',
  `course_max_size` int(10) unsigned NOT NULL COMMENT '最大容量',
  `course_exam_date` datetime DEFAULT NULL COMMENT '考试时间',
  `course_exam_location` varchar(32) DEFAULT NULL COMMENT '考试地点',
  PRIMARY KEY (`course_id`) USING BTREE,
  KEY `fk_course_teacher_id` (`course_teacher_id`) USING BTREE,
  KEY `idx_course_name` (`course_name`) USING BTREE,
  CONSTRAINT `fk_course_teacher_id` FOREIGN KEY (`course_teacher_id`) REFERENCES `teacher` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE `department` (
  `department_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '系Id',
  `department_name` varchar(32) NOT NULL COMMENT '系名称',
  PRIMARY KEY (`department_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE `major` (
  `major_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '专业Id',
  `major_department_id` int(10) unsigned NOT NULL COMMENT '系Id',
  `major_name` varchar(32) NOT NULL COMMENT '专业名称',
  PRIMARY KEY (`major_id`) USING BTREE,
  KEY `fk_major_department_id` (`major_department_id`) USING BTREE,
  KEY `idx_major_name` (`major_name`) USING BTREE,
  CONSTRAINT `fk_major_department_id` FOREIGN KEY (`major_department_id`) REFERENCES `department` (`department_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE `student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生Id',
  `student_class_id` int(10) unsigned NOT NULL COMMENT '班级Id',
  `student_number` char(12) NOT NULL COMMENT '学号',
  `student_name` varchar(20) NOT NULL COMMENT '姓名',
  `student_password` char(32) NOT NULL COMMENT '密码',
  `student_email` varchar(64) DEFAULT NULL COMMENT '电子邮箱',
  `student_birthday` datetime DEFAULT NULL COMMENT '生日',
  `student_sex` tinyint(1) unsigned NOT NULL COMMENT '性别',
  `student_last_login_time` datetime DEFAULT NULL COMMENT '最近登录时间',
  PRIMARY KEY (`student_id`) USING BTREE,
  UNIQUE KEY `idx_student_number` (`student_number`) USING BTREE,
  KEY `fk_student_class_id` (`student_class_id`) USING BTREE,
  KEY `idx_student_name` (`student_name`) USING BTREE,
  CONSTRAINT `fk_student_class_id` FOREIGN KEY (`student_class_id`) REFERENCES `class` (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE `student_course` (
  `sc_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '选课Id',
  `sc_student_id` int(10) unsigned NOT NULL COMMENT '学生Id',
  `sc_course_id` int(10) unsigned NOT NULL COMMENT '课程Id',
  `sc_daily_score` int(10) unsigned DEFAULT NULL COMMENT '日常表现分',
  `sc_exam_score` int(10) unsigned DEFAULT NULL COMMENT '期末测试分',
  `sc_score` int(10) unsigned DEFAULT NULL COMMENT '总成绩',
  PRIMARY KEY (`sc_id`) USING BTREE,
  KEY `fk_sc_course_id` (`sc_course_id`) USING BTREE,
  KEY `fk_sc_student_id` (`sc_student_id`) USING BTREE,
  CONSTRAINT `fk_sc_course_id` FOREIGN KEY (`sc_course_id`) REFERENCES `course` (`course_id`),
  CONSTRAINT `fk_sc_student_id` FOREIGN KEY (`sc_student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE `teacher` (
  `teacher_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '教师Id',
  `teacher_department_id` int(10) unsigned NOT NULL COMMENT '系Id',
  `teacher_number` char(12) NOT NULL COMMENT '工号',
  `teacher_name` varchar(20) NOT NULL COMMENT '教师姓名',
  `teacher_password` char(32) NOT NULL COMMENT '密码',
  PRIMARY KEY (`teacher_id`) USING BTREE,
  UNIQUE KEY `idx_teacher_number` (`teacher_number`) USING BTREE,
  KEY `fk_teacher_department_id` (`teacher_department_id`) USING BTREE,
  CONSTRAINT `fk_teacher_department_id` FOREIGN KEY (`teacher_department_id`) REFERENCES `department` (`department_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
BEGIN;
LOCK TABLES `course`.`admin` WRITE;
DELETE FROM `course`.`admin`;
INSERT INTO `course`.`admin` (`admin_id`,`admin_username`,`admin_password`,`admin_privilege`) VALUES (1, 'admin', '81a5f5a9bfde4cdcb5b9fe1f8508df2a', 255),(2, 'azure99', '81a5f5a9bfde4cdcb5b9fe1f8508df2a', 96),(3, 'test', '12345678', 255),(4, 'me', '123456', 255);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `course`.`class` WRITE;
DELETE FROM `course`.`class`;
INSERT INTO `course`.`class` (`class_id`,`class_major_id`,`class_grade`,`class_name`) VALUES (1, 1, 2017, '计信1班'),(2, 1, 2019, '计信1班'),(3, 4, 2017, '数学1班'),(4, 5, 2017, '测试班级1'),(5, 5, 2017, '测试班级2'),(6, 5, 2017, '测试班级3'),(24, 2, 2017, '软件1班'),(25, 2, 2017, '软件2班');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `course`.`course` WRITE;
DELETE FROM `course`.`course`;
INSERT INTO `course`.`course` (`course_id`,`course_teacher_id`,`course_name`,`course_grade`,`course_time`,`course_location`,`course_credit`,`course_selected_count`,`course_max_size`,`course_exam_date`,`course_exam_location`) VALUES (1, 1, 'C语言程序设计', 2017, '1-1-2', '信工楼107', 5, 17, 50, NULL, NULL),(2, 1, 'Java程序设计', 2019, '1-3-2', '信工楼106', 4, 0, 30, NULL, NULL),(3, 1, '数据库实用技术', 2017, '2-3-2', 'C区202', 2, 1, 50, NULL, NULL),(4, 1, 'ASP.Net开发', 2017, '5-5-3', 'E区315', 2, 1, 1, NULL, NULL),(5, 1, 'Spring企业级开发', 2017, '3-9-2', '信工楼101', 3, 1, 10, NULL, NULL),(6, 3, '数据库概论', 2017, '3-1-2', 'C区106', 5, 0, 40, NULL, NULL),(7, 3, 'Photoshop', 2017, '2-3-2', 'C区222', 2, 0, 20, NULL, NULL),(8, 4, '计算机网络', 2017, '4-1-3', '信工楼109', 5, 1, 20, NULL, NULL);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `course`.`department` WRITE;
DELETE FROM `course`.`department`;
INSERT INTO `course`.`department` (`department_id`,`department_name`) VALUES (1, '计算机系'),(2, '数学系'),(3, '物理系'),(4, '化学系'),(5, '测试系1'),(6, '测试系2'),(7, '测试系3'),(15, '临时系1'),(16, '临时系2');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `course`.`major` WRITE;
DELETE FROM `course`.`major`;
INSERT INTO `course`.`major` (`major_id`,`major_department_id`,`major_name`) VALUES (1, 1, '计算机科学与技术'),(2, 1, '软件工程'),(3, 1, '信息工程'),(4, 2, '应用数学'),(5, 2, '数学师范'),(6, 5, '测试专业1'),(7, 5, '测试专业2'),(8, 5, '测试专业3'),(9, 5, '测试专业4'),(10, 5, '测试专业5'),(11, 5, '测试专业6'),(12, 5, '测试专业7'),(13, 5, '测试专业8'),(14, 5, '测试专业9'),(15, 5, '测试专业10'),(16, 6, '临时专业1'),(17, 6, '临时专业2'),(18, 6, '临时专业3'),(19, 6, '临时专业4'),(20, 6, '临时专业5'),(21, 6, '临时专业6'),(22, 6, '临时专业7'),(23, 6, '临时专业8'),(24, 6, '临时专业9'),(25, 6, '临时专业10'),(26, 7, '演示专业1'),(27, 7, '演示专业2'),(28, 7, '演示专业3'),(29, 7, '演示专业4'),(30, 7, '演示专业5'),(31, 7, '演示专业6'),(32, 7, '演示专业7'),(33, 7, '演示专业8'),(34, 7, '演示专业9'),(35, 7, '演示专业10'),(36, 7, '演示专业11'),(37, 7, '演示专业12'),(38, 7, '演示专业13'),(39, 7, '演示专业14'),(40, 7, '演示专业15'),(41, 7, '演示专业16'),(42, 7, '演示专业17'),(43, 7, '演示专业18'),(44, 7, '演示专业19'),(45, 7, '演示专业20');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `course`.`student` WRITE;
DELETE FROM `course`.`student`;
INSERT INTO `course`.`student` (`student_id`,`student_class_id`,`student_number`,`student_name`,`student_password`,`student_email`,`student_birthday`,`student_sex`,`student_last_login_time`) VALUES (1, 25, '201711010001', '邓佳阳', '7191300714d497b41bdbbc4ee42d338f', '', '1999-07-26 00:00:00', 1, '2020-06-14 10:57:37'),(2, 25, '201711010002', '许全', '7191300714d497b41bdbbc4ee42d338f', NULL, NULL, 1, NULL),(3, 25, '201711010003', '杨宁昊', '7191300714d497b41bdbbc4ee42d338f', NULL, NULL, 0, NULL),(4, 25, '201711010004', '朱生涛', '7191300714d497b41bdbbc4ee42d338f', NULL, NULL, 0, NULL),(5, 1, '201711010005', '李同学3', '81a5f5a9bfde4cdcb5b9fe1f8508df2a', NULL, NULL, 0, NULL),(12, 1, '201711010012', '张同学1', '81a5f5a9bfde4cdcb5b9fe1f8508df2a', NULL, NULL, 1, NULL),(30, 25, '001', '邓佳阳', '7191300714d497b41bdbbc4ee42d338f', NULL, NULL, 1, '2020-06-11 20:42:12');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `course`.`student_course` WRITE;
DELETE FROM `course`.`student_course`;
INSERT INTO `course`.`student_course` (`sc_id`,`sc_student_id`,`sc_course_id`,`sc_daily_score`,`sc_exam_score`,`sc_score`) VALUES (2, 2, 1, NULL, NULL, NULL),(3, 3, 1, NULL, NULL, NULL),(4, 4, 1, NULL, NULL, NULL),(5, 5, 1, NULL, NULL, NULL),(6, 6, 1, NULL, NULL, NULL),(7, 7, 1, NULL, NULL, NULL),(8, 8, 1, NULL, NULL, NULL),(9, 9, 1, NULL, NULL, NULL),(10, 10, 1, NULL, NULL, NULL),(11, 11, 1, NULL, NULL, NULL),(12, 12, 1, NULL, NULL, NULL),(13, 13, 1, NULL, NULL, NULL),(14, 14, 1, NULL, NULL, NULL),(15, 15, 1, NULL, NULL, NULL),(16, 16, 1, NULL, NULL, NULL),(17, 17, 1, NULL, NULL, NULL),(18, 18, 1, NULL, NULL, NULL),(19, 1, 3, 98, 100, 99),(21, 30, 4, NULL, NULL, NULL),(22, 30, 8, NULL, NULL, NULL),(23, 1, 5, NULL, NULL, NULL);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `course`.`teacher` WRITE;
DELETE FROM `course`.`teacher`;
INSERT INTO `course`.`teacher` (`teacher_id`,`teacher_department_id`,`teacher_number`,`teacher_name`,`teacher_password`) VALUES (1, 1, '201711010001', '张三', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(2, 3, '201711020001', '宋老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(3, 1, '201711010002', '宋老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(4, 1, '201711010003', '张老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(5, 1, '201711010004', '吕老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(6, 1, '201711010005', '王老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(7, 1, '201711010006', '丁老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(8, 1, '201711010007', '高老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a'),(9, 1, '201711010008', '赵老师', '81a5f5a9bfde4cdcb5b9fe1f8508df2a');
UNLOCK TABLES;
COMMIT;
